#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
数据库管理工具
"""
import sqlite3
import os
import sys

def show_menu():
    """显示菜单"""
    print("\n🗄️ 数据库管理工具")
    print("=" * 30)
    print("1. 查看数据库概览")
    print("2. 查看用户列表")
    print("3. 重置admin用户")
    print("4. 删除所有用户")
    print("5. 查看表结构")
    print("6. 执行SQL查询")
    print("7. 导出数据")
    print("0. 退出")
    print("=" * 30)

def view_overview():
    """查看数据库概览"""
    db_path = 'dev.db'
    
    if not os.path.exists(db_path):
        print("❌ 数据库文件不存在")
        return
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 获取数据库大小
        size = os.path.getsize(db_path)
        print(f"📊 数据库大小: {size / 1024:.2f} KB")
        
        # 获取所有表
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        
        print(f"📋 表数量: {len(tables)}")
        print("表列表:")
        for i, table in enumerate(tables, 1):
            cursor.execute(f"SELECT COUNT(*) FROM {table[0]}")
            count = cursor.fetchone()[0]
            print(f"  {i:2}. {table[0]:20} ({count} 条记录)")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 查看数据库时出错: {e}")

def view_users():
    """查看用户列表"""
    try:
        conn = sqlite3.connect('dev.db')
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT id, username, email, is_superuser, is_staff, is_active, 
                   date_joined, last_login
            FROM auth_user
        """)
        users = cursor.fetchall()
        
        if users:
            print("👥 用户列表:")
            print("ID | 用户名     | 邮箱              | 超级用户 | 员工 | 激活 | 注册时间         | 最后登录")
            print("-" * 95)
            for user in users:
                print(f"{user[0]:2} | {user[1]:10} | {user[2]:17} | {user[3]:8} | {user[4]:4} | {user[5]:4} | {user[6][:16]} | {user[7] or 'Never'}")
        else:
            print("没有用户数据")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 查看用户时出错: {e}")

def reset_admin():
    """重置admin用户"""
    try:
        conn = sqlite3.connect('dev.db')
        cursor = conn.cursor()
        
        # 删除现有admin用户
        cursor.execute("DELETE FROM auth_user WHERE username='admin'")
        
        # 创建新admin用户
        from datetime import datetime
        now = datetime.now().isoformat()
        
        cursor.execute("""
            INSERT INTO auth_user (username, email, password, is_superuser, is_staff, is_active, date_joined)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, ('admin', 'admin@mes.com', 'pbkdf2_sha256$260000$dummy$dummy', 1, 1, 1, now))
        
        conn.commit()
        conn.close()
        
        print("✅ admin用户已重置")
        print("用户名: admin")
        print("⚠️  请使用Django命令设置密码:")
        print("python manage.py changepassword admin --settings=settings_ultra_minimal")
        
    except Exception as e:
        print(f"❌ 重置admin用户失败: {e}")

def delete_all_users():
    """删除所有用户"""
    confirm = input("⚠️  确定要删除所有用户吗？(输入 'YES' 确认): ")
    if confirm == 'YES':
        try:
            conn = sqlite3.connect('dev.db')
            cursor = conn.cursor()
            
            cursor.execute("DELETE FROM auth_user")
            conn.commit()
            
            deleted = cursor.rowcount
            conn.close()
            
            print(f"✅ 已删除 {deleted} 个用户")
            
        except Exception as e:
            print(f"❌ 删除用户失败: {e}")
    else:
        print("❌ 操作已取消")

def show_table_structure():
    """显示表结构"""
    table_name = input("请输入表名: ")
    
    try:
        conn = sqlite3.connect('dev.db')
        cursor = conn.cursor()
        
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = cursor.fetchall()
        
        if columns:
            print(f"\n📊 表结构: {table_name}")
            print("列名              | 类型      | 非空 | 默认值 | 主键")
            print("-" * 60)
            
            for col in columns:
                print(f"{col[1]:16} | {col[2]:9} | {col[3]:4} | {col[4] or 'NULL':6} | {col[5]:4}")
        else:
            print(f"❌ 表 '{table_name}' 不存在")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 查看表结构失败: {e}")

def execute_sql():
    """执行SQL查询"""
    print("请输入SQL查询 (输入 'exit' 退出):")
    
    while True:
        sql = input("SQL> ").strip()
        
        if sql.lower() == 'exit':
            break
        
        if not sql:
            continue
        
        try:
            conn = sqlite3.connect('dev.db')
            cursor = conn.cursor()
            
            cursor.execute(sql)
            
            if sql.lower().startswith('select'):
                results = cursor.fetchall()
                if results:
                    for row in results:
                        print(row)
                else:
                    print("没有结果")
            else:
                conn.commit()
                print(f"✅ 查询执行成功，影响 {cursor.rowcount} 行")
            
            conn.close()
            
        except Exception as e:
            print(f"❌ SQL执行失败: {e}")

def export_data():
    """导出数据"""
    table_name = input("请输入要导出的表名: ")
    
    try:
        conn = sqlite3.connect('dev.db')
        cursor = conn.cursor()
        
        cursor.execute(f"SELECT * FROM {table_name}")
        rows = cursor.fetchall()
        
        # 获取列名
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = [col[1] for col in cursor.fetchall()]
        
        # 导出为CSV格式
        filename = f"{table_name}_export.csv"
        with open(filename, 'w', encoding='utf-8') as f:
            # 写入列名
            f.write(','.join(columns) + '\n')
            
            # 写入数据
            for row in rows:
                f.write(','.join(str(cell) for cell in row) + '\n')
        
        print(f"✅ 数据已导出到 {filename}")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 导出数据失败: {e}")

def main():
    """主函数"""
    while True:
        show_menu()
        
        try:
            choice = input("请选择操作 (0-7): ").strip()
            
            if choice == '0':
                print("👋 再见！")
                break
            elif choice == '1':
                view_overview()
            elif choice == '2':
                view_users()
            elif choice == '3':
                reset_admin()
            elif choice == '4':
                delete_all_users()
            elif choice == '5':
                show_table_structure()
            elif choice == '6':
                execute_sql()
            elif choice == '7':
                export_data()
            else:
                print("❌ 无效选择")
                
        except KeyboardInterrupt:
            print("\n👋 再见！")
            break
        except Exception as e:
            print(f"❌ 操作失败: {e}")
        
        input("\n按回车键继续...")

if __name__ == "__main__":
    main() 